import datetime
import os

import pandas as pd

import Core.Config as Config

# ---本例介绍数据库的操作---

# 执行复杂SQL语句
def Query_Data_with_SQL(database):
    # sql = "SELECT * FROM factor.cap where Symbol = '000001.SZ' and date >= 20200101 and date <= 20200201"
    sql = "SELECT * FROM financial_data.mutualfund_report where Symbol = '000001.OF' and report_date >= 20200101 and date <= 20201231"
    documents = database.FindWithSQL("financial_data", "", sql)
    for item in documents:
        print(item)


# 读取数据
def Query_Data(database):
    # 以下语句相当于
    # Select * From factor.cap where symbol = "000001.SZ" and date >= 20200101 and date <= 20200201 order by date desc
    sql = "SELECT * FROM financial_data.mutualfund_report where Symbol = '000001.OF' and report_date >= 20200101 and date <= 20201231"

    # 构造筛选条件
    filter = []
    filter.append(["symbol", "000001.OF"])
    filter.append(["date", ">=", datetime.datetime(2020, 1, 1)])
    filter.append(["date", "<=", datetime.datetime(2020, 12, 31)])

    # 构造排序条件
    sort = [("date", -1)] # 因为可以多列排序，所以采用的是List结构，-1是降序

    # 读取成list
    documents = database.Find(databaseName="financial_data",  # 库名
                              tableName="mutualfund_report",  # 表名
                              filter=filter,  # 筛选条件
                              sort=sort)  # 排序
    #
    for item in documents:
        print(item)

    # 读取成 dataframe
    df = database.GetDataFrame(databaseName="financial_data",  # 库名
                               tableName="mutualfund_report",  # 表名
                               filter=filter,  # 筛选条件
                               sort=sort)  # 排序

    for index, row in df.iterrows():
        print(row)


# 插入数据（无则插入，有则更新）
# 数据将被插入在 factor.test_table 表
def Upsert_Data(database):
    # 插入的数据List
    documents = []
    #
    for i in range(10):
        document = {}  #  一个document相当于数据表的一行

        # document 的 key 和数据表中的字段是对应的
        document["key2"] = "test_" + str(i) # 唯一标识
        document["value"] = i

        # 填充至数据List
        documents.append(document)

    # 插入数据
    database.Upsert_Many(databaseName="factor",  # 库名
                         tableName="test_table",   # 表名
                         targets=[],  # 保持为空
                         documents=documents)  # 需要插入的数据List


# 插入数据
def Insert_Data(database):
    pass


# 读取数据，计算因子
def calculate_a_factor(database):
    # 最简单读取数据
    df = database.GetDataFrame("Factor", "a_sys_factor", {"Name": "UnRestrict_Amt"}, projection=["date", "value"])
    print(df)
    df = df[:10]

    # 简单计算
    df["value"] = df["value"] * 10

    # 最简单储存数据
    df["name"] = "stock_level"
    df["key2"] = df["name"] + "_" + df["date"].apply(lambda x: x.strftime('%Y-%m-%d'))
    print(df)
    #
    database.SaveDataFrame("factor", "test", df)


def check_data(database):
    datetime1 = datetime.datetime(2021,9,30)
    symbol = "600028.SH"
    dic_data = {}
    dic_data["key2"] = "600028.SH_Fundamental_2021-09-30"
    dic_data["symbol"] = "600028.SH"
    dic_data["period"] = 3
    dic_data["datetime"] = datetime1
    dic_data["utc_datetime"] = datetime1

    dic_data["np_belongto_parcomsh"] = 101
    list_data = [dic_data]

    df = pd.DataFrame(data=list_data)

    df_get = database.GetDataFrame("financial_data", "stock_fundamental_basic", {"symbol": symbol, "report_date":datetime1} )

    df_test = database.GetDataFrame("financial_data", "stock_fundamental_basic",
                                    projection=["symbol", "report_date"],
                                    filter=[("symbol", symbol), ("report_date", datetime1)])
    a= 0
    #
    # database.SaveDataFrame("financial_data", "stock_fundamental_basic", df)


if __name__ == '__main__':
    # 连接数据库
    path_filename = os.getcwd() + "\..\Config\config_local.json"  # 数据库配置文件位置 # 本地数据库
    # path_filename = os.getcwd() + "\..\Config\config_tencent_cloud.json"  # 数据库配置文件位置  # 云端数据库

    database = Config.create_database(database_type="MySQL", config_file=path_filename, config_field="MySQL")  # 连接数据库

    #
    # Query_Data_with_SQL(database)
    # Query_Data(database)
    # Upsert_Data(database)

    # calculate_a_factor(database)

    check_data(database)